﻿using System;
using System.Linq;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System.Data;
using System.Collections.Generic;
using System.Web.UI;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.ServiceModel;
using System.Net;
using System.Web;
using WcfResultPoint;
using System.Security.Principal;
using Newtonsoft.Json;
using System.Web.UI.WebControls;

namespace ImportExamResult.Layouts.ImportExamResult
{
    public partial class ImportDiemThiPage : LayoutsPageBase
    {
        const string xlsxConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
        const string xlsConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=YES;\"";
        const string TableKey = "Table";
        const string Key = "FileName";
        const string StdKey = "StdKey";
        const string DefaultUniversityKey = "DefaultUniversity";
        const int CountPattern = 200;

        readonly string Url = System.Configuration.ConfigurationManager.AppSettings["Url"];
        private string SqlConnectionString = System.Configuration.ConfigurationManager.
                ConnectionStrings["TuyenSinhConnectionString"].ConnectionString;
        private ChannelFactory<httpWcfResultPoint> ChannelFactory;
        private httpWcfResultPoint Channel;

        #region Members & Properties

        private DataTable _tblTruongDaiHoc;
        private static Dictionary<string, string> _mapCaptis = new Dictionary<string, string>();
        private static Dictionary<string, string> _mapFields = new Dictionary<string, string>();

        #endregion

        #region Constructors & Dispose

        public ImportDiemThiPage()
        {
            if (_mapFields.Count == 0)
            {
                _mapFields.Add(Fields.Std.MaTruong, Fields.Dbf.MaTruong);
                _mapFields.Add(Fields.Std.MaNganh, Fields.Dbf.MaNganh);
                _mapFields.Add(Fields.Std.KhoiThi, Fields.Dbf.KhoiThi);
                _mapFields.Add(Fields.Std.SBD, Fields.Dbf.SBD);
                _mapFields.Add(Fields.Std.HoTen, Fields.Dbf.HoTen);
                _mapFields.Add(Fields.Std.NgaySinh, Fields.Dbf.NgaySinh);
                _mapFields.Add(Fields.Std.DM1, Fields.Dbf.DM1);
                _mapFields.Add(Fields.Std.DM2, Fields.Dbf.DM2);
                _mapFields.Add(Fields.Std.DM3, Fields.Dbf.DM3);
                _mapFields.Add(Fields.Std.TongDiem, Fields.Dbf.Dtc);
                _mapFields.Add(Fields.Std.DM4, Fields.Dbf.DM4);
                _mapFields.Add(Fields.Std.DM5, Fields.Dbf.DM5);
                _mapFields.Add(Fields.Std.DM6, Fields.Dbf.DM6);
                _mapFields.Add(Fields.Std.Ten, Fields.Dbf.Ten);

                _mapCaptis.Add(Fields.Std.MaTruong, "Mã trường:");
                _mapCaptis.Add(Fields.Std.MaNganh, "Mã ngành:");
                _mapCaptis.Add(Fields.Std.KhoiThi, "Khối thi:");
                _mapCaptis.Add(Fields.Std.SBD, "Số báo danh:");
                _mapCaptis.Add(Fields.Std.HoTen, "Họ tên:");
                _mapCaptis.Add(Fields.Std.NgaySinh, "Ngày sinh:");
                _mapCaptis.Add(Fields.Std.DM1, "Môn 1:");
                _mapCaptis.Add(Fields.Std.DM2, "Môn 2:");
                _mapCaptis.Add(Fields.Std.DM3, "Môn 3:");
                _mapCaptis.Add(Fields.Std.TongDiem, "Tổng cộng:");
                _mapCaptis.Add(Fields.Std.DM4, "Môn 4:");
                _mapCaptis.Add(Fields.Std.DM5, "Môn 5:");
                _mapCaptis.Add(Fields.Std.DM6, "Môn 6:");
                _mapCaptis.Add(Fields.Std.Ten, "Cột tên phụ:");
            }

            try
            {
                // Open channel
                Uri serviceUri = new Uri(Url);
                BasicHttpBinding basicHttpBinding = new BasicHttpBinding();
                basicHttpBinding.Security.Mode = BasicHttpSecurityMode.None;
                basicHttpBinding.MaxReceivedMessageSize = int.MaxValue;
                basicHttpBinding.ReaderQuotas.MaxStringContentLength = int.MaxValue;
                basicHttpBinding.ReaderQuotas.MaxArrayLength = int.MaxValue;

                ChannelFactory = new ChannelFactory<httpWcfResultPoint>(basicHttpBinding, new EndpointAddress(serviceUri));
                //ChannelFactory.Credentials.UserName.UserName = @"tnotest\spadmin";
                //ChannelFactory.Credentials.UserName.Password = @"Admin123!";

                //Create a channel
                Channel = ChannelFactory.CreateChannel();

                string json = Channel.GetUniversities();
                _tblTruongDaiHoc = JsonConvert.DeserializeObject<DataTable>(json);
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
        }

        public override void Dispose()
        {
            ChannelFactory.Close();

            base.Dispose();
        }

        #endregion

        #region Events

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                string json = Channel.GetEmployees();
                this.ddlFileSender.DataSource = JsonConvert.DeserializeObject<DataTable>(json);
                this.ddlFileSender.DataValueField = "MSNV";
                this.ddlFileSender.DataTextField = "TenNV";
                this.ddlFileSender.DataBind();
            }
            this.litErrorMessage.Text = string.Empty;

            // Event import exam result
            this.btnSend.Click += new EventHandler(btnSend_Click);
            this.btnStandard.Click += new EventHandler(btnStandard_Click);
            this.btnExport.Click += new EventHandler(btnExport_Click);
            this.btnExecute.Click += new EventHandler(btnExecute_Click);
            this.btnOk.Click += new EventHandler(btnOk_Click);
            this.btnCancel.Click += new EventHandler(btnCancel_Click);

            // Event standard scores
            this.btnStdClear.Click += new EventHandler(btnStdClear_Click);
            this.btnStdProcess.Click += new EventHandler(btnStdProcess_Click);
            this.btnStdSave.Click += new EventHandler(btnStdSave_Click);
            this.btnStdExcel.Click += new EventHandler(btnStdExcel_Click);
        }

        #region Exam Result Events

        protected void btnSend_Click(object sender, EventArgs e)
        {
            try
            {
                if (string.IsNullOrEmpty(fileUpload.FileName)) return;

                // Upload file
                if (Upload(fileUpload.PostedFile, fileUpload.PostedFile.ContentLength))
                {
                    string json = string.Empty;
                    SessionUtils.Set(Key, fileUpload.FileName);
                    FileInfo fileInfo = new FileInfo(fileUpload.FileName);
                    switch (fileInfo.Extension.ToLower())
                    {
                        case ".xls":
                        case ".xlsx":
                            json = Channel.ProcessExcel(fileUpload.FileName);
                            //this.pnlSheet.Visible = true;
                            break;
                        case ".dbf":
                            json = Channel.ProcessVFoxPro(fileUpload.FileName);
                            //this.pnlSheet.Visible = false;
                            ResetDbfStandardFields();
                            break;
                        default:
                            break;
                    }

                    ResultModel resultModel = JsonConvert.DeserializeObject<ResultModel>(json);
                    SessionUtils.Set(TableKey, resultModel);
                    SetSource(resultModel);

                    btnStandard.Enabled = true;
                    btnMapField.Enabled = true;
                    btnExport.Enabled = false;
                    btnExecute.Enabled = false;
                    grpCustomize.Visible = true;
                }
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
        }

        protected void btnStandard_Click(object sender, EventArgs e)
        {
            try
            {
                string defaultUniversity = (this.chkAddUniversityId.Checked ? this.txtUniversityId.Text : string.Empty);
                SessionUtils.Set(DefaultUniversityKey, defaultUniversity);
                string json = Channel.StandData(SessionUtils.Get<string>(Key), _mapFields, defaultUniversity);

                if (json == string.Empty)
                {
                    throw new Exception("Chuẩn hóa file gặp lỗi (Có thể bạn chưa thực hiện map field chuẩn hóa đúng)." + Environment.NewLine
                                    + "Vui lòng kiểm tra lại!");
                }

                ResultModel resultModel = JsonConvert.DeserializeObject<ResultModel>(json);
                SetSource(resultModel);

                this.litUniversityId.Text = resultModel.Table.Rows[0][Fields.Std.MaTruong].ToString();
                this.txtUniversityName.Text = GetUniversityName(this.litUniversityId.Text);
                this.grpUniversity.Visible = true;

                btnExport.Enabled = true;
                btnExecute.Enabled = true;
                btnStandard.Enabled = false;
                btnMapField.Enabled = false;
                this.grpCustomize.Visible = false;
                //this.pnlSheet.Visible = false;
            }
            catch (Exception ex)
            {
                litErrorMessage.Text = ex.Message;
            }
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            //string attachment = String.Format("attachment; filename=diemthi{0:yyyyMMdd}.xls", DateTime.Now);
            //Response.ClearContent();
            //Response.AddHeader("content-disposition", attachment);
            //Response.ContentType = "application/ms-excel";
            //Response.Charset = string.Empty;
            //Response.ContentEncoding = System.Text.Encoding.Unicode;
            //Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());

            //System.IO.StringWriter stw = new System.IO.StringWriter();
            //HtmlTextWriter htextw = new HtmlTextWriter(stw);
            //var form = GetDataToExport();
            //this.Controls.Add(form);
            //form.RenderControl(htextw);

            //Response.Write(stw.ToString());
            //Response.End();

            try
            {
                FileData data = Channel.DownloadExcelFile(SessionUtils.Get<string>(Key), _mapFields, SessionUtils.Get<string>(DefaultUniversityKey));
                string attachment = String.Format("attachment; filename=diemthi{0:yyyyMMdd}.xls", DateTime.Now);
                Response.ClearContent();
                Response.AddHeader("content-disposition", attachment);
                Response.ContentType = "application/ms-excel";
                Response.OutputStream.Write(data.BufferData, 0, data.BufferData.Length);
                Response.End();
            }
            catch (Exception ex)
            {
                litErrorMessage.Text = ex.ToString();
            }
        }

        protected void btnExecute_Click(object sender, EventArgs e)
        {
            ErrorCode errorCode = Channel.SaveExamResult(SessionUtils.Get<string>(Key), _mapFields,
                this.litUniversityId.Text, this.txtUniversityName.Text, true, SessionUtils.Get<string>(DefaultUniversityKey),
                this.ddlFileSender.SelectedValue, this.txtNote.Text);
            if (errorCode == ErrorCode.ExistResult || errorCode == ErrorCode.ExistUniversity)
            {
                this.pnlConfirmExecute.Visible = true;
                this.litMask.Text = "<div class='modalOverlay'>";
                switch (errorCode)
                {
                    case ErrorCode.ExistResult:
                        this.litConfirmMessage.Text = string.Format("<b class='red'>Điểm của trường {0} đã được đưa vào trước đó. Nếu tiếp tục bạn sẽ đưa vào dữ liệu trùng?</b>", this.litUniversityId.Text);
                        break;
                    case ErrorCode.ExistUniversity:
                        this.litConfirmMessage.Text = string.Format("<b class='blue'>Trường {0} đã được đổ điểm trước đó. Bạn có muốn tiếp tục đưa dữ liệu vào không?</b>", this.litUniversityId.Text);
                        break;
                }

            }
            else if (errorCode != ErrorCode.Success)
            {
                this.litErrorMessage.Text = "Quá trình đổ điểm gặp lỗi!";

                // TO-DO
                this.pnlConfirmExecute.Visible = false;
                this.litMask.Text = string.Empty;
                this.litConfirmMessage.Text = string.Empty;
            }
            else
            {
                this.litErrorMessage.Text = "Quá trình đổ điểm XONG!";

                // Success
                this.pnlConfirmExecute.Visible = false;
                this.litMask.Text = string.Empty;
                this.litConfirmMessage.Text = string.Empty;
            }
        }

        protected void btnOk_Click(object sender, EventArgs e)
        {
            ErrorCode errorCode = Channel.SaveExamResult(SessionUtils.Get<string>(Key), _mapFields,
                this.litUniversityId.Text, this.txtUniversityName.Text, false, SessionUtils.Get<string>(DefaultUniversityKey),
                this.ddlFileSender.SelectedValue, this.txtNote.Text);
            if (errorCode != ErrorCode.Success)
            {
                // TO-DO
                this.litErrorMessage.Text = "Quá trình đổ điểm gặp lỗi!";
            }
            else
            {
                // Success
                this.litErrorMessage.Text = "Quá trình đổ điểm XONG!";
            }

            this.pnlConfirmExecute.Visible = false;
            this.litMask.Text = string.Empty;
            this.litConfirmMessage.Text = string.Empty;

        }

        protected void btnCancel_Click(object sender, EventArgs e)
        {
            this.pnlConfirmExecute.Visible = false;
            this.litMask.Text = string.Empty;
            this.litConfirmMessage.Text = string.Empty;
        }

        #endregion

        #region Standard Scores Events
        protected void btnStdClear_Click(object sender, EventArgs e)
        {
            this.txtWordFile.Text = string.Empty;
        }

        string[] _specialCharacter = { "trường", "đại học", "cao đẳng", "học viện" };

        protected void btnStdProcess_Click(object sender, EventArgs e)
        {
            string text = this.txtWordFile.Text;

            string[] arrayText = text.Split('\n');
            if (arrayText.Length > 0)
            {
                int i = 0;
                if (!arrayText[0].Contains('\t'))
                {
                    // University
                    string temp = arrayText[0].Replace("\r", string.Empty);
                    temp = temp.Replace("\t", string.Empty);
                    temp = temp.Trim().ToLower();
                    foreach (var item in _specialCharacter)
                    {
                        temp = temp.Replace(item, string.Empty);
                    }

                    string json = Channel.GetUniversityBy(temp.Trim());
                    DataTable tableUniv = JsonConvert.DeserializeObject<DataTable>(json);
                    if (tableUniv.Rows.Count == 0)
                    {
                        tableUniv = _tblTruongDaiHoc;
                    }
                    this.ddlSuggestUniversity.DataSource = tableUniv;
                    this.ddlSuggestUniversity.DataValueField = "ID";
                    this.ddlSuggestUniversity.DataTextField = "Title";
                    this.ddlSuggestUniversity.DataBind();

                    i++;
                }

                DataTable table = new DataTable();
                table.Columns.Add("ID_Truong");
                table.Columns.Add("MaNganh");
                table.Columns.Add("TenNganh");
                table.Columns.Add("KhoiThi");
                table.Columns.Add("Diem");
                table.Columns.Add("Nam");
                table.Columns.Add("Note");
                table.Columns.Add("CreatedDate");
                table.Columns.Add("OrderIndex");

                // Skip header
                i++;
                for (int j = 1; i < arrayText.Length; i++, j++)
                {
                    string temp = arrayText[i].Replace("\r", string.Empty);
                    string[] cols = temp.Split('\t');
                    if (cols.Length > 4)
                    {
                        DataRow row = table.NewRow();
                        row["Nam"] = DateTime.Today.Year;
                        row["CreatedDate"] = DateTime.Now;
                        row["OrderIndex"] = j;
                        row["MaNganh"] = cols[1].Trim();
                        row["TenNganh"] = cols[2].Trim();
                        row["KhoiThi"] = cols[3].Replace(" ", string.Empty).Replace(",", ", ");
                        row["Diem"] = Convert.ToSingle(cols[4].Trim());

                        if (cols.Length > 5)
                        {
                            row["Note"] = cols[5].Trim();
                        }

                        table.Rows.Add(row);
                    }
                }

                this.btnStdSave.Enabled = table.Rows.Count > 0;
                this.btnStdExcel.Enabled = table.Rows.Count > 0;

                this.grdStdResult.DataSource = table;
                this.grdStdResult.DataBind();

                SessionUtils.Set(StdKey, table);
            }
        }

        protected void btnStdSave_Click(object sender, EventArgs e)
        {
            DataTable table = SessionUtils.Get<DataTable>(StdKey);
            foreach (DataRow row in table.Rows)
            {
                row["ID_Truong"] = ddlSuggestUniversity.SelectedValue;
            }

            string json = JsonConvert.SerializeObject(table, Formatting.Indented);
            ErrorCode errorCode = Channel.SaveStdScores(json);
            switch (errorCode)
            {
                case ErrorCode.Exception:
                    litStdMsg.Text = "Lỗi lưu điểm chuẩn";
                    break;
                case ErrorCode.Success:
                    litStdMsg.Text = "Đã lưu xong điểm chuẩn";
                    break;
            }
        }

        protected void btnStdExcel_Click(object sender, EventArgs e)
        {
            try
            {
                DataTable table = SessionUtils.Get<DataTable>(StdKey);
                foreach (DataRow row in table.Rows)
                {
                    row["ID_Truong"] = ddlSuggestUniversity.SelectedValue;
                }

                string json = JsonConvert.SerializeObject(table, Formatting.Indented);
                FileData data = Channel.DownloadFile(json);
                string attachment = String.Format("attachment; filename=diemchuan{0:yyyyMMdd}.xls", DateTime.Now);
                Response.ClearContent();
                Response.AddHeader("content-disposition", attachment);
                Response.ContentType = "application/ms-excel";
                Response.OutputStream.Write(data.BufferData, 0, data.BufferData.Length);
                Response.End();
            }
            catch (Exception ex)
            {
                litStdMsg.Text = ex.ToString();
            }
        } 

        #endregion

        #endregion

        #region Methods

        protected bool Upload(HttpPostedFile file, long actualFileSize)
        {
            int filePosition = 0;
            int filePart = 16 * 1024; //Each hit 16 kb file to avoid any serialization issue when transfering  data across WCF

            //Create buffer size to send to service based on filepart size
            byte[] bufferData = new byte[filePart];

            //Set the posted file data to file stream.
            Stream fileStream = file.InputStream;

            //Create the service client

            try
            {
                long actualFileSizeToUpload = actualFileSize;
                //Start reading the file from the specified position.
                fileStream.Position = filePosition;
                int fileBytesRead = 0;

                //Upload file data in parts until filePosition reaches the actual file end or size.
                while (filePosition != actualFileSizeToUpload)
                {
                    // read the next file part i.e. another 100 kb of data 
                    fileBytesRead = fileStream.Read(bufferData, 0, filePart);
                    if (fileBytesRead != bufferData.Length)
                    {
                        filePart = fileBytesRead;
                        byte[] bufferedDataToWrite = new byte[fileBytesRead];
                        //Copy the buffered data into bufferedDataToWrite
                        Array.Copy(bufferData, bufferedDataToWrite, fileBytesRead);
                        bufferData = bufferedDataToWrite;
                    }

                    //Populate the data contract to send it to the service method
                    bool fileDataWritten = Channel.UploadFile(new FileData { FileName = file.FileName, BufferData = bufferData, FilePosition = filePosition });
                    if (!fileDataWritten)
                    {
                        break;
                    }

                    //Update the filePosition position to continue reading data from that position back to server
                    filePosition += fileBytesRead;
                }
            }
            catch (FaultException)
            {
                //Log data to database or file system here
                return false;
            }
            finally
            {
                //Close the fileStream once all done.
                fileStream.Close();
            }
            return true;
        }

        private System.Web.UI.HtmlControls.HtmlForm GetDataToExport()
        {
            var gridView = new System.Web.UI.WebControls.GridView();
            string json = Channel.GetDataToExport(SessionUtils.Get<string>(Key), _mapFields, SessionUtils.Get<string>(DefaultUniversityKey));
            ResultModel resultModel = JsonConvert.DeserializeObject<ResultModel>(json);
            gridView.DataSource = resultModel.Table;
            gridView.DataBind();

            var form = new System.Web.UI.HtmlControls.HtmlForm();
            form.Controls.Add(gridView);

            return form;
        }

        private System.Web.UI.HtmlControls.HtmlForm GetStdDataToExport()
        {
            foreach (GridViewRow item in grdStdResult.Rows)
            {
                item.Cells[1].Text = ddlSuggestUniversity.SelectedValue;
            }

            var form = new System.Web.UI.HtmlControls.HtmlForm();
            form.Controls.Add(grdStdResult);

            return form;
        }

        private string GetUniversityName(string universityId)
        {
            DataRow[] rows = _tblTruongDaiHoc.Select("ID = '" + universityId + "'");
            if (rows.Length > 0)
            {
                return rows[0]["Title"].ToString();
            }
            return string.Empty;
        }

        private static void ResetDbfStandardFields()
        {
            _mapFields[Fields.Std.MaTruong] = Fields.Dbf.MaTruong;
            _mapFields[Fields.Std.KhoiThi] = Fields.Dbf.KhoiThi;
            _mapFields[Fields.Std.MaNganh] = Fields.Dbf.MaNganh;
            _mapFields[Fields.Std.HoTen] = Fields.Dbf.HoTen;
            _mapFields[Fields.Std.NgaySinh] = Fields.Dbf.NgaySinh;
            _mapFields[Fields.Std.SBD] = Fields.Dbf.SBD;
            _mapFields[Fields.Std.DM1] = Fields.Dbf.DM1;
            _mapFields[Fields.Std.DM2] = Fields.Dbf.DM2;
            _mapFields[Fields.Std.DM3] = Fields.Dbf.DM3;
            _mapFields[Fields.Std.DM4] = Fields.Dbf.DM4;
            _mapFields[Fields.Std.DM5] = Fields.Dbf.DM5;
            _mapFields[Fields.Std.DM6] = Fields.Dbf.DM6;
            _mapFields[Fields.Std.TongDiem] = Fields.Dbf.Dtc;
            _mapFields[Fields.Std.Ten] = Fields.Dbf.Ten;
        }

        private void SetSource(ResultModel model)
        {
            Grid.DataSource = model.Table;
            Grid.DataBind();
            Grid.FooterRow.Cells[1].Text = "Số dòng: " + model.TotalRecord;
            litErrorMessage.Text = "Số dòng: " + model.TotalRecord;
        }

        #endregion

        #region Script Methods

        [Serializable]
        public class Template
        {
            public string key { get; set; }
            public string value { get; set; }
            public string caption { get; set; }

            public Template(string key, string value)
            {
                this.key = key;
                this.value = value;
                this.caption = null;
            }

            public Template(string key, string value, string caption)
            {
                this.key = key;
                this.value = value;
                this.caption = caption;
            }
        }

        [System.Web.Services.WebMethod]
        public static object GetTableColumns()
        {
            ResultModel resultModel = SessionUtils.Get<ResultModel>(TableKey);
            IList<string> temp = new List<string>();
            IDictionary<string, Template> columns = new Dictionary<string, Template>();
            foreach (DataColumn item in resultModel.Table.Columns)
            {
                if (!_mapFields.Values.Contains(item.ColumnName.ToLower()))
                {
                    if (columns.ContainsKey(item.ColumnName)) continue;
                    columns.Add(item.ColumnName, new Template(item.ColumnName, item.ColumnName));
                }
                else
                {
                    temp.Add(item.ColumnName.ToLower());
                }
            }
            var columns1 = new List<Template>();
            foreach (var item in _mapFields)
            {
                if (temp.Contains(item.Value))
                    columns1.Add(new Template(item.Key, item.Value, _mapCaptis[item.Key]));
                else
                    columns1.Add(new Template(item.Key, string.Empty, _mapCaptis[item.Key]));
            }
            return new { Items = columns.Values.ToList(), SelectedItems = columns1 };
        }

        [System.Web.Services.WebMethod]
        public static void SaveColumns(object[] list)
        {
            foreach (IDictionary<string, object> item in list)
            {
                _mapFields[item["key"].ToString()] = item["value"].ToString();
            }
        }
        #endregion

    }

    [Serializable]
    public class ResultModel
    {
        public int TotalRecord { get; set; }
        public DataTable Table { get; set; }
    }
}
